﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreData;
using EntityData;
using Helper;
using Services.BusinessRules;
namespace Services
{

    public partial class ProductService
    {
       readonly IProductEntity entity;

       public ProductService()
       {
           this.entity = new ProductEntity();

       }
       #region SQLCmd

       private string StrGetCodebyGroup = @"DECLARE @GCode Varchar(50)
                                            SET @GCode = (SELECT Product_Group.Code FROM Product_Group WHERE Product_Group.ID={0})
                                            SELECT (@GCode + RIGHT('00'+CONVERT(varchar(3),(ISNULL(MAX(RIGHT(Product.Code,3)),0)+1)),3)) AS Code FROM Product
                                            WHERE LEFT(Product.Code,2)=@GCode";
        //fill product to voucher out
       private string StrGetListProductVD = @"select TI.ID,TI.Code,TI.Name,TI.Output_Price,TI.Input_Price,TI.Output_UnitID,TI.Input_UnitID,
isnull((TI.InStockQuantity)-isnull(sum(vod.Quantity),0),0) as InStockQuantity
from ( 
select p.ID,P.Output_Price,p.Input_Price,p.Code,p.Name,p.Output_UnitID,p.Input_UnitID,bs.NewQuantity,
isnull(sum(vid.Quantity),0)+ bs.NewQuantity as InStockQuantity
from Product p
left join Stock_VoucherInDetail vid on P.ID=vid.ProductID
left join BeginStock_Detail bs on bs.ProductID=p.ID
group by p.ID,vid.UnitID,p.Name,p.Output_UnitID,p.Input_UnitID,P.Output_Price,p.Code,P.Input_Price,bs.NewQuantity 
) TI
left join Stock_VoucherOutDetail vod on vod.ProductID=TI.ID
{0}
group by TI.ID,TI.Output_UnitID,TI.Input_UnitID,TI.InStockQuantity,TI.Output_Price,TI.Code,TI.Name,TI.Input_Price ";
       private string StrGetPriceWithUnit = @"SELECT (P.Input_Price / PUC.Ratio) as Input_Price FROM Product P LEFT JOIN Product_UnitChange PUC ON P.ID=PUC.ProductID WHERE PUC.UnitChangeID= {0} AND P.ID={1}";
       //lay sp cho nhap kho
       private string StrGetList_frmSelectProduct = @"select  P.ID,P.Code,P.Name,P.Input_Price as Price
,P.Input_UnitID,PG.Name as ProductGroup,PU.Name as UnitName
from Product P
left join Product_Group PG on P.Product_GroupID=PG.ID
left join Product_Unit PU on PU.ID=P.Input_UnitID {0}";
        //lay sp cho xuat kho ban
       private string StrGetList_frmSelectProductStockUnit = @"select  TI.ID,TI.Code,TI.Name,TI.Output_Price,TI.Input_Price,TI.Output_UnitID,TI.Input_UnitID,
isnull((TI.InStockQuantity)-isnull(sum(vod.Quantity),0),0) as InStockQuantity
from ( 
select p.ID,P.Output_Price,p.Input_Price,p.Code,p.Name,p.Output_UnitID,p.Input_UnitID,
sum(isnull(vid.Quantity,0))+ bs.NewQuantity as InStockQuantity
from Product p
left join Stock_VoucherInDetail vid on P.ID=vid.ProductID
left join BeginStock_Detail bs on bs.ProductID=p.ID
group by p.ID,vid.UnitID,p.Name,p.Output_UnitID,p.Input_UnitID,P.Output_Price,p.Code,P.Input_Price,bs.NewQuantity
) TI
left join Stock_VoucherOutDetail vod on vod.ProductID=TI.ID
{0}
group by TI.ID,TI.Output_UnitID,TI.Input_UnitID,TI.InStockQuantity,TI.Output_Price,TI.Code,TI.Name,TI.Input_Price
Order by ID ASC";
        
        
        //danh sach the kho
       private string StrGetList_StockReport = @"select TI.ID,TI.UnitID,TI.UnitName,TI.ProductName as Name,TI.Price,TI.ProductGroup,TI.Code,
isnull((TI.InStockQuantity)-isnull(sum(vod.Quantity),0),0) + TI.NewQuantity as InStockQuantity,
isnull(sum(vod.Quantity),0) as OutputQuantity,isnull((TI.InStockQuantity),0) as InputQuantity
,TI.Product_GroupID
from ( 
select p.ID,p.Output_UnitID as UnitID,P.Output_Price as Price,p.Code
,pus.Name as UnitName,p.Name as ProductName,pg.Name as ProductGroup,p.Product_GroupID,
isnull(sum(vid.Quantity),0)as InStockQuantity,bs.NewQuantity
from Product p
left join Stock_VoucherInDetail vid on P.ID=vid.ProductID
left join Product_Unit pus on pus.ID=p.Output_UnitID
left join Product_Group pg on pg.ID=p.Product_GroupID
left join Stock_VoucherIn vi on vi.ID=vid.Stock_VoucherInID
left join BeginStock_Detail bs on bs.ProductID=p.ID
group by p.ID,vid.UnitID,p.Name,p.Output_UnitID,pus.Name,P.Output_Price,pg.Name,p.Code,p.Product_GroupID,bs.NewQuantity
) TI
left join Stock_VoucherOutDetail vod on vod.ProductID=TI.ID
{0}
group by TI.ID,TI.UnitID,TI.UnitName,TI.ProductName,TI.InStockQuantity,TI.Price,TI.ProductGroup,TI.Code,TI.Product_GroupID,TI.NewQuantity";
        
        //thẻ kho
       private string StrGetDetail_StockReport = @"WITH CTE AS
(select ROW_NUMBER() OVER(ORDER BY VoucherDate ASC) AS STT,V.ProductID
,V.VoucherNo,V.VoucherDate
,V.InputQuantity+V.NewQuantity as InputQuantity,V.OutputQuantity
,V.UnitID
FROM
(select vid.ProductID
,vi.VoucherInNo as VoucherNo,vi.VoucherDate
,NULL as OutputQuantity,vid.Quantity as InputQuantity
,vid.UnitID
,vi.Note as VoucherDescription,bs.NewQuantity
from Stock_VoucherInDetail vid
left join Stock_VoucherIn vi on vi.ID=vid.Stock_VoucherInID
left join Product P on P.ID=vid.ProductID
left join BeginStock_Detail bs on bs.ProductID=p.ID
union
select vod.ProductID
,vo.VoucherOutNo as VoucherNo,vo.VoucherDate
,vod.Quantity as OutputQuantity,NULL as InputQuantity
,vod.UnitID
,vo.Note as VoucherDescription,bs.NewQuantity
from Stock_VoucherOutDetail vod
left join Product P on P.ID=vod.ProductID
left join Stock_VoucherOut vo on vo.ID=vod.Stock_VoucherOutID
left join BeginStock_Detail bs on bs.ProductID=p.ID
)V
where V.ProductID= {0})
SELECT c.STT,c.ProductID,c.VoucherNo,c.VoucherDate
,c.InputQuantity
,c.OutputQuantity
,(select SUM(CTE.InputQuantity) from CTE where CTE.STT<=c.STT)- isnull(c.OutputQuantity,0) as InStockQuantity
FROM CTE c
left join Product p on p.ID=c.ProductID
";
        private string StrGetProductBeginStock = @"Select P.ID, P.Code,P.Name, PU.Name as UnitName,PU.ID as UnitID 
,P.Output_Price,PG.Name as ProductGroup
From Product P
left join Product_Unit PU on PU.ID=P.Input_UnitID
left join Product_Group PG on PG.ID =P.Product_GroupID
where P.ID NOT IN (SELECT BD.ProductID FROM BeginStock_Detail BD LEFT JOIN BeginStock B ON B.ID=BD.BeginStockID WHERE B.BeginStockNo='KTTD') ";
       #endregion
       #region Main Method

       /// <summary>
       /// Save 
       /// </summary>
       /// <param name="_model"></param>
       /// <returns></returns>
       public long Save(object _model,ref string mess)
       {
           try
           {
               if (Validate((Product)_model, ref mess))
               {
                   var id = long.Parse(_model.GetType().GetProperty("ID").GetValue(_model, null).ToString());
                   if (id == 0)
                   {
                       return entity.Save((CoreData.Product)_model, Table.Product.ToString());
                   }
                   else
                   {
                       return entity.Update((CoreData.Product)_model, Table.Product.ToString());
                   }
               }
               else
               {
                   return -1;
               }
           }
           catch { return -1; }
       }

       /// <summary>
       /// Get by ID
       /// </summary>
       /// <param name="_id"></param>
       /// <returns></returns>
       public CoreData.Product GetByID(long _id)
       {
           try
           {
               return entity.GetByCusTomSQL(String.Format(SQLCommand.GetAllProducts," Product.ID="+_id)).FirstOrDefault();
           }
           catch { return null; }
       }

       /// <summary>
       /// Get List
       /// </summary>
       /// <returns></returns>
       public IList<CoreData.Product> GetList()
       {
           try
           {
               return entity.GetByCusTomSQL(String.Format(SQLCommand.GetAllProducts, " 1=1")).ToList();
           }
           catch { return null; }

       }

       /// <summary>
       /// Get List with Linq
       /// </summary>
       /// <returns></returns>
       public IList<CoreData.Product> GetListByLINQ(Func<CoreData.Product, Boolean> _where)
       {
           try
           {
               return entity.GetMany(_where, Table.Product.ToString()).ToList();
           }
           catch { return null; }
       }
       /// <summary>
       /// Get One with Linq
       /// </summary>
       /// <returns></returns>
       public CoreData.Product GetOneByLINQ(Func<CoreData.Product, Boolean> _where)
       {
           try
           {
               return entity.Get(_where, Table.Product.ToString());
           }
           catch { return null; }
       }

       /// <summary>
       /// Get List with search string
       /// </summary>
       /// <returns></returns>
       public IList<CoreData.Product> GetList(string _searchstring)
       {
           //search theo tieu chi nao do         
           try
           {
               return entity.GetByCusTomSQL(String.Format(SQLCommand.GetAllProducts, _searchstring)).ToList();
           }
           catch { return null; }
       }


       /// <summary>
       /// Delete
       /// </summary>
       /// <param name="_model"></param>
       /// <returns></returns>
       public bool Delete(object _model)
       {
           try
           {
               entity.Delete((CoreData.Product)_model, Table.Product.ToString());
               return true;
           }
           catch { return false; }
       }


       #endregion



        #region Other Method
       /// <summary>
       /// Get new Code for Product by GroupID
       /// </summary>
       /// <param name="_groupid">ProductGroupID</param>
       /// <returns>Product Code</returns>
       public string GetProductCodeByGroup(long _groupid)
       {
           try
           {
               return entity.GetByCusTomSQL(String.Format(StrGetCodebyGroup, _groupid)).FirstOrDefault().Code;
           }
           catch { return null; }
       }
       public List<Product> GetProductInListID(List<long> ProductSeletedIDs)
       {
           try
           {
               if (ProductSeletedIDs.Count > 0)
               {
                   string _where = string.Format("where TI.ID IN ({0})", string.Join(",", ProductSeletedIDs.ToArray()));
                   return entity.GetByCusTomSQL(String.Format(StrGetListProductVD, _where)).ToList();
               }
               else
               {
                   return entity.GetByCusTomSQL(String.Format(StrGetListProductVD, " where 1=1")).ToList();
               }               
           }
           catch { return null; }
       }
       
       public List<Product> GetProductList_frmSelectedProduct(List<long> _ListProductSeletedID, long _Product_ProviderID, string search)
       {
           try
           {
               if (_ListProductSeletedID.Count>0)
               {
                   string _where = string.Format("where P.Product_ProviderID={0} AND P.ID NOT IN ({1}) {2}",_Product_ProviderID,string.Join(",", _ListProductSeletedID.ToArray()),search);
                   return entity.GetByCusTomSQL(String.Format(StrGetList_frmSelectProduct, _where)).ToList();
               }
               else
               {
                   string _where = string.Format("where P.Product_ProviderID={0} {1}", _Product_ProviderID, search);
                   return entity.GetByCusTomSQL(String.Format(StrGetList_frmSelectProduct, _where)).ToList();
               }               
               
           }
           catch { return null; }
       }
       public List<Product> GetProductList_frmSelectedProduct(List<long> _ListProductSeletedID, string search)
       {
           try
           {
               if (_ListProductSeletedID.Count > 0)
               {
                   string _where = string.Format(" where TI.ID NOT IN ({0}) {1}", string.Join(",", _ListProductSeletedID.ToArray()),search);
                   return entity.GetByCusTomSQL(String.Format(StrGetList_frmSelectProductStockUnit, _where)).ToList();
               }
               else
               {
                   return entity.GetByCusTomSQL(String.Format(StrGetList_frmSelectProductStockUnit, " where 1=1 " + search)).ToList();
               }

           }
           catch { return null; }
       }
       public List<Product> GetProductBeginStock_frmSelectedProduct(List<long> _ListProductSeletedID,string search)
       {
           try
           {
               if (_ListProductSeletedID.Count > 0)
               {
                   string _where = string.Format(" where P.ID NOT IN ({0}) {1}", string.Join(",", _ListProductSeletedID.ToArray()), search);
                   return entity.GetByCusTomSQL(String.Format(StrGetProductBeginStock, _where)).ToList();
               }
               else
               {
                   return entity.GetByCusTomSQL(String.Format(StrGetProductBeginStock, " where 1=1 "+search)).ToList();
               }

           }
           catch { return null; }
       }
        public List<Product> GetProductList_StockReport(Product _ProductEntity)
       {
           try
           {
               string _searchStr = "";
               if (_ProductEntity.Product_GroupID > 0)
                   _searchStr += " TI.Product_GroupID = " + _ProductEntity.Product_GroupID + " AND";
               if (_ProductEntity.Code != "")
                   _searchStr += " TI.Code like '%"+_ProductEntity.Code+"%' OR";
               if (_ProductEntity.Name != "")
                   _searchStr += " TI.ProductName like N'%" + _ProductEntity.Name + "%' COLLATE Latin1_General_CI_AI OR";
               if (_searchStr != "")
                   _searchStr = _searchStr.Substring(0, _searchStr.Length - 3);
               if(_searchStr!="")
                    return entity.GetByCusTomSQL(String.Format(StrGetList_StockReport, " where "+_searchStr)).ToList();
               else
                   return entity.GetByCusTomSQL(String.Format(StrGetList_StockReport, " where 1=1")).ToList();
           }
           catch { return null; }
       }
        public List<Product> GetProductDetail_StockReport(long ProductID)
        {
            try
            {
                return entity.GetByCusTomSQL(String.Format(StrGetDetail_StockReport, ProductID.ToString())).ToList();
            }
            catch { return null; }
        }
       public decimal GetPriceByUnit(long _UnitChangeID,long _ProductID)
       {
           try
           {
               return entity.GetByCusTomSQL(String.Format(StrGetPriceWithUnit, _UnitChangeID,_ProductID)).FirstOrDefault().Input_Price;
           }
           catch { return 0; }
       }
       bool Validate(Product obj, ref string mess)
       {
           new ValidateRequired("Mã hàng hóa", "Code", obj.Code);
           new ValidateRequired("Tên hàng hóa", "Name", obj.Name);
           if (obj.Input_UnitID == 0)
           {
               new ValidateRequired("Đơn vị nhập", "Đơn vị nhập", "");
           }
           if (obj.Output_UnitID == 0)
           {
               new ValidateRequired("Đơn vị xuất", "Đơn vị xuất", "");
           }
           if (obj.Stock_UnitID == 0)
           {
               new ValidateRequired("Đơn vị lưu kho", "Đơn vị lưu kho", "");
           }
           if (obj.Product_GroupID == 0)
           {
               new ValidateRequired("Nhóm hàng hóa", "Nhóm hàng hóa", "");
           }
           //new ValidateExist("Mã hàng hóa", "Code", "Code", obj, GetList().Cast<object>().ToList());
           //   new ValidateExist("Tên học viên", "Name", "Name", obj, GetList().Cast<object>().ToList());
           new ValidateLenght("Giá nhập", "Input_Price", obj.Input_Price.ToString(), 15);
           new ValidateLenght("Giá xuất", "Output_Price", obj.Output_Price.ToString(), 15);
           mess = BusinessObject.Validate();
           BusinessObject.Clear();

           if (mess == null)
           {

               var code = GetByCode(obj.Code);
               if (code != null && (obj.ID != code.ID))
               {
                   mess = "Mã hàng hóa bị trùng";
               }
           }
           if (mess == null)
           {

               var code = GetByName(obj.Name);
               if (code != null && (obj.ID != code.ID))
               {
                   mess = "Tên hàng hóa bị trùng";
               }
           }
           return mess == null ? true : false;
       }
       public CoreData.Product GetByCode(string Code)
       {
           try
           {
               return entity.GetByCusTomSQL(String.Format(SQLCommand.GetAllProducts, " Product.Code='" + Code + "'")).ToList()[0];
               //return entity.GetById(_id, Table.Class.ToString());
           }
           catch { return null; }
       }
       public CoreData.Product GetByName(string Code)
       {
           try
           {
               return entity.GetByCusTomSQL(String.Format(SQLCommand.GetAllProducts, " Product.Name='" + Code + "'")).ToList()[0];
               //return entity.GetById(_id, Table.Class.ToString());
           }
           catch { return null; }
       }
       #endregion 
    
    }
         
}
